malloy logoMalloy Documentation
Getting Started
document The Tao of Malloy
document Key Features
Language Reference
document Introduction
document Basics
document Models
document Queries
document Expressions
document Ordering and Limiting
document Shape Definition
document Joins
document Filters
document Filter Syntax
document Time Ranges
document Nesting
document Imports and Exports
document Apply Operator
Common Patterns
document Year Over Year Analysis
document Foreign Sums and Averages
document Percent of Total
document Cohort Analysis
document Pivot Limits
document Sessionized Data
Visualizations
document Bar Charts
document Line Charts
document Shape Maps
document Segment Maps
document Scatter Charts
Examples
document eCommerce Analysis
document NTSB Flight Database
document The Name Game
document Google Analytics GA360
Iowa Liquor - An Analysis
document About the Data Set
document The Malloy Data Model
document Basic Calculations
document Analysis: Products and Brands
document Bucketing and Mapping
document Dashboards
previouseCommerce Analysis
The Name Gamenext

NTSB Flight Database examples

The follow examples all run against the model at the bottom of this page OR you can find the source code here.

Airport Dashboard

Where can you fly from SJC? For each destination; Which carriers? How long have they been flying there? Are they ontime?

explore flights : [origin.code : 'SJC']
| airport_dashboard
code
LAX
destination
LOS ANGELES INTL
flight_count
58,815
carriers_by_month
200020022004dep_month0200400flight_countAmericanAmerican EagleSky WestSouthwestUnitednickname
routes_map
58,815flight_count
delay_by_hour_of_day
05101520dep_hour02040flight_count31489delay
code
SAN
destination
SAN DIEGO INTL-LINDBERGH FLD
flight_count
34,702
carriers_by_month
200020022004dep_month0100200300flight_countAmericanAmerican EagleContinentalSouthwestnickname
routes_map
34,702flight_count
delay_by_hour_of_day
05101520dep_hour0102030flight_count31730delay
code
SNA
destination
JOHN WAYNE AIRPORT-ORANGE COUNTY
flight_count
30,907
carriers_by_month
200020022004dep_month0100200flight_countAmericanAmerican EagleSouthwestnickname
routes_map
30,907flight_count
delay_by_hour_of_day
05101520dep_hour01020flight_count200400600delay
code
LAS
destination
MC CARRAN INTL
flight_count
25,825
carriers_by_month
200020022004dep_month0100200flight_countAmerica WestAmericanSouthwestnickname
routes_map
25,825flight_count
delay_by_hour_of_day
05101520dep_hour01020flight_count31739delay
code
SEA
destination
SEATTLE-TACOMA INTL
flight_count
24,864
carriers_by_month
200020022004dep_month0100200300flight_countAlaskaAmericanHawaiianSouthwestnickname
routes_map
24,864flight_count
delay_by_hour_of_day
05101520dep_hour01020flight_count200400600delay
code
PHX
destination
PHOENIX SKY HARBOR INTL
flight_count
24,310
carriers_by_month
200020022004dep_month0100200flight_countAmerica WestAmericanAmerican EagleSouthwestnickname
routes_map
24,310flight_count
delay_by_hour_of_day
05101520dep_hour0510flight_count31577delay
code
ORD
destination
CHICAGO O'HARE INTL
flight_count
21,997
carriers_by_month
200020022004dep_month0100200flight_countAmericanUnitednickname
routes_map
21,997flight_count
delay_by_hour_of_day
05101520dep_hour051015flight_count31534delay
code
PDX
destination
PORTLAND INTL
flight_count
21,562
carriers_by_month
200020022004dep_month0100200flight_countAlaskaAmericanSouthwestnickname
routes_map
21,562flight_count
delay_by_hour_of_day
05101520dep_hour01020flight_count31769delay
code
BUR
destination
BURBANK-GLENDALE-PASADENA
flight_count
17,557
carriers_by_month
200020022004dep_month0100200flight_countSouthwestnickname
routes_map
17,557flight_count
delay_by_hour_of_day
05101520dep_hour010203040flight_count100200300delay
code
ONT
destination
ONTARIO INTL
flight_count
17,358
carriers_by_month
200020022004dep_month0100200300flight_countSky WestSouthwestnickname
routes_map
17,358flight_count
delay_by_hour_of_day
05101520dep_hour01020flight_count31335delay

Carrier Dashboard

Tell me everything about a carrier. How many destinations?, flights? hubs? What kind of planes to they use? How many flights over time? What are the major hubs? For each destionation, How many flights? Where can you? Have they been flying there long? Increasing or decresing year by year? Any seasonality?

explore flights : [carriers.nickname : 'Jetblue']
| carrier_dashboard
destination_count
33
flight_count
267,963
by_manufacturer
0204060aircraft_countAIRBUSAIRBUS INDUSTRIEEMBRAERnull1,151161,208flight_count
by_month
200320042005dep_month05,00010,000flight_count
hubs
hub destination_​count
JFK - JOHN F KENNEDY INTL 29
BOS - GENERAL EDWARD LAWRENCE LOGAN INTL 14
LGB - LONG BEACH /DAUGHERTY FIELD/ 9
OAK - METROPOLITAN OAKLAND INTL 7
FLL - FORT LAUDERDALE/HOLLYWOOD INTL 7
IAD - WASHINGTON DULLES INTERNATIONAL 6
EWR - NEWARK INTL 6
PBI - PALM BEACH INTL 4
ONT - ONTARIO INTL 4
MCO - ORLANDO INTL 3
code
JFK
origin
JOHN F KENNEDY INTL
city
NEW YORK
flight_count
96,992
destinations_by_month
200320042005dep_time_month0200400flight_countnullBOS - GENERAL EDWA…BQN - RAFAEL HERNA…BTV - BURLINGTON I…BUF - BUFFALO NIAG…BUR - BURBANK-GLEN…DEN - DENVER INTLEWR - NEWARK INTLFLL - FORT LAUDERD…LAS - MC CARRAN IN…LAX - LOS ANGELES …LGB - LONG BEACH /…MCO - ORLANDO INTLMSY - NEW ORLEANS …OAK - METROPOLITAN…ONT - ONTARIO INTLPBI - PALM BEACH I…PDX - PORTLAND INTLPHX - PHOENIX SKY …PSE - MERCEDITAROC - GREATER ROCH…RSW - SOUTHWEST FL…SAN - SAN DIEGO IN…SEA - SEATTLE-TACO…SJC - SAN JOSE INT…SJU - LUIS MUNOZ M…SLC - SALT LAKE CI…SMF - SACRAMENTO I…SYR - SYRACUSE HAN…TPA - TAMPA INTLname
routes_map
113,281flight_count
year_over_year
24681012dep_month01,0002,0003,0004,000flight_countDec 31, 2002Dec 31, 2004dep_year
code
LGB
origin
LONG BEACH /DAUGHERTY FIELD/
city
LONG BEACH
flight_count
23,799
destinations_by_month
200320042005dep_time_month0100200flight_countATL - THE WILLIAM …BOS - GENERAL EDWA…FLL - FORT LAUDERD…IAD - WASHINGTON D…JFK - JOHN F KENNE…LAS - MC CARRAN IN…OAK - METROPOLITAN…ONT - ONTARIO INTLSLC - SALT LAKE CI…name
routes_map
2,0004,0006,000flight_count
year_over_year
24681012dep_month0200400600800flight_countDec 31, 2002Dec 31, 2004dep_year
code
FLL
origin
FORT LAUDERDALE/HOLLYWOOD INTL
city
FORT LAUDERDALE
flight_count
22,641
destinations_by_month
200320042005dep_time_month0200400flight_countBOS - GENERAL EDWA…BTV - BURLINGTON I…EWR - NEWARK INTLIAD - WASHINGTON D…JFK - JOHN F KENNE…LGA - LA GUARDIALGB - LONG BEACH /…name
routes_map
113,286flight_count
year_over_year
24681012dep_month05001,000flight_countDec 31, 2002Dec 31, 2004dep_year
code
OAK
origin
METROPOLITAN OAKLAND INTL
city
OAKLAND
flight_count
16,528
destinations_by_month
200320042005dep_time_month0100200flight_countATL - THE WILLIAM …BOS - GENERAL EDWA…IAD - WASHINGTON D…JFK - JOHN F KENNE…LAX - LOS ANGELES …LGB - LONG BEACH /…ONT - ONTARIO INTLname
routes_map
2,0004,0006,000flight_count
year_over_year
24681012dep_month0200400flight_countDec 31, 2002Dec 31, 2004dep_year
code
MCO
origin
ORLANDO INTL
city
ORLANDO
flight_count
13,006
destinations_by_month
200320042005dep_time_month0100200300400flight_countBOS - GENERAL EDWA…EWR - NEWARK INTLJFK - JOHN F KENNE…name
routes_map
2,0004,0006,0008,000flight_count
year_over_year
24681012dep_month0200400600flight_countDec 31, 2002Dec 31, 2004dep_year
code
BOS
origin
GENERAL EDWARD LAWRENCE LOGAN INTL
city
BOSTON
flight_count
11,889
destinations_by_month
2004AprilJulyOctober2005AprilJulyOctoberdep_time_month0100200flight_countDEN - DENVER INTLFLL - FORT LAUDERD…JFK - JOHN F KENNE…LAS - MC CARRAN IN…LAX - LOS ANGELES …LGB - LONG BEACH /…MCO - ORLANDO INTLOAK - METROPOLITAN…ONT - ONTARIO INTLPBI - PALM BEACH I…RSW - SOUTHWEST FL…SEA - SEATTLE-TACO…SJC - SAN JOSE INT…TPA - TAMPA INTLname
routes_map
5001,0001,5002,0002,500flight_count
year_over_year
24681012dep_month05001,000flight_countDec 31, 2003Dec 31, 2004dep_year
code
IAD
origin
WASHINGTON DULLES INTERNATIONAL
city
WASHINGTON
flight_count
9,642
destinations_by_month
200320042005dep_time_month050100flight_countFLL - FORT LAUDERD…JFK - JOHN F KENNE…LGB - LONG BEACH /…OAK - METROPOLITAN…SAN - SAN DIEGO IN…SMF - SACRAMENTO I…name
routes_map
1,0002,0003,000flight_count
year_over_year
24681012dep_month0100200300flight_countDec 31, 2002Dec 31, 2004dep_year
code
TPA
origin
TAMPA INTL
city
TAMPA
flight_count
8,143
destinations_by_month
200320042005dep_time_month0100200flight_countBOS - GENERAL EDWA…EWR - NEWARK INTLJFK - JOHN F KENNE…name
routes_map
2,0004,0006,000flight_count
year_over_year
24681012dep_month0100200300400flight_countDec 31, 2002Dec 31, 2004dep_year
code
PBI
origin
PALM BEACH INTL
city
WEST PALM BEACH
flight_count
8,034
destinations_by_month
200320042005dep_time_month0100200300400flight_countBOS - GENERAL EDWA…EWR - NEWARK INTLJFK - JOHN F KENNE…LGA - LA GUARDIAname
routes_map
2,0004,0006,000flight_count
year_over_year
24681012dep_month0200400flight_countDec 31, 2002Dec 31, 2004dep_year
code
LAS
origin
MC CARRAN INTL
city
LAS VEGAS
flight_count
7,557
destinations_by_month
200320042005dep_time_month050100150200flight_countBOS - GENERAL EDWA…JFK - JOHN F KENNE…LGB - LONG BEACH /…name
routes_map
1,0002,0003,0004,000flight_count
year_over_year
24681012dep_month0100200300flight_countDec 31, 2002Dec 31, 2004dep_year

Kayak Example Query

Suppose you wanted to build a website like Kayak. Let's assume that the data we have is in the future instead ofthe past. The query below will fetch all the data needed to render a Kayak page in a singe query.

explore flights : [
  origin.code : 'SJC',
  destination.code : 'LAX'|'BUR',
  dep_time : @2004-01-01
]
| kayak
carriers by_​hour flights
nickname flight_​count
Southwest 16
American Eagle 11
Sky West 6
dep_​hour flight_​count
6 2
8 2
9 3
10 3
11 1
13 3
14 2
15 3
16 2
17 5
18 2
19 4
21 1
dep_​minute name flight_​num origin_​code destination_​code manufacturer model
2004-01-01 21:05 Southwest Airlines 2866 SJC LAX BOEING 737-317
2004-01-01 19:55 Sky West Airlines 6991 SJC LAX BOMBARDIER INC CL-600-2B19
2004-01-01 19:54 Southwest Airlines 1437 SJC LAX BOEING 737-7H4
2004-01-01 19:40 Southwest Airlines 3353 SJC BUR BOEING 737-7H4
2004-01-01 19:20 American Eagle Airlines 3216 SJC LAX EMBRAER EMB-135KL
2004-01-01 18:20 Southwest Airlines 2484 SJC LAX BOEING 737-3H4
2004-01-01 18:17 Southwest Airlines 565 SJC BUR BOEING 737-317
2004-01-01 17:51 American Eagle Airlines 3152 SJC LAX EMBRAER EMB-135KL
2004-01-01 17:36 Sky West Airlines 6987 SJC LAX BOMBARDIER INC CL-600-2B19
2004-01-01 17:30 Southwest Airlines 495 SJC LAX BOEING 737-3H4
2004-01-01 17:14 American Eagle Airlines 3206 SJC LAX EMBRAER EMB-135KL
2004-01-01 17:00 Southwest Airlines 1664 SJC LAX BOEING 737-3H4
2004-01-01 16:35 Southwest Airlines 990 SJC BUR BOEING 737-3Q8
2004-01-01 16:10 Southwest Airlines 1383 SJC LAX BOEING 737-7H4
2004-01-01 15:32 Sky West Airlines 6980 SJC LAX BOMBARDIER INC CL-600-2B19
2004-01-01 15:30 Southwest Airlines 624 SJC LAX BOEING 737-7H4
2004-01-01 15:13 American Eagle Airlines 3168 SJC LAX EMBRAER EMB-135KL
2004-01-01 14:34 American Eagle Airlines 3160 SJC LAX EMBRAER EMB-135KL
2004-01-01 14:02 American Eagle Airlines 3148 SJC LAX EMBRAER EMB-135KL
2004-01-01 13:25 Southwest Airlines 197 SJC BUR BOEING 737-5H4
2004-01-01 13:17 Sky West Airlines 6948 SJC LAX BOMBARDIER INC CL-600-2B19
2004-01-01 13:15 Southwest Airlines 2226 SJC LAX BOEING 737-3H4
2004-01-01 11:56 American Eagle Airlines 3198 SJC LAX EMBRAER EMB-135KL
2004-01-01 10:35 Southwest Airlines 603 SJC BUR BOEING 737-3G7
2004-01-01 10:24 American Eagle Airlines 3200 SJC LAX EMBRAER EMB-135KL
2004-01-01 10:15 Southwest Airlines 1593 SJC LAX BOEING 737-3Q8
2004-01-01 09:40 Southwest Airlines 1478 SJC BUR BOEING 737-5H4
2004-01-01 09:31 American Eagle Airlines 3196 SJC LAX EMBRAER EMB-135KL
2004-01-01 09:30 Southwest Airlines 1323 SJC LAX BOEING 737-3H4
2004-01-01 08:52 Sky West Airlines 6946 SJC LAX BOMBARDIER INC CL-600-2B19
2004-01-01 08:24 American Eagle Airlines 3194 SJC LAX EMBRAER EMB-135KL
2004-01-01 06:34 Sky West Airlines 6993 SJC LAX BOMBARDIER INC CL-600-2B19
2004-01-01 06:14 American Eagle Airlines 3192 SJC LAX EMBRAER EMB-135KL

Sessionizing Flight Data.

You can think of flight data as event data. The below is a classic map/reduce roll up of the filght data by carrier and day, plane and day, and individual events for each plane.

  sessionize is (reduce : [carrier:'WN', dep_time: @2002-03-03]
    dep_time.`date`
    carrier
    flight_count
    plane is (reduce top 20
      tail_num
      flight_count
      flights is (reduce order by 2
        tail_num
        dep_minute is dep_time.minute
        origin_code
        destination_code
      )
    )
  )
explore flights : [carrier:'WN', dep_time: @2002-03-03]
| sessionize
flight_​date carrier daily_​flight_​count per_​plane_​data
2002-03-03 WN 2,411
tail_​num plane_​flight_​count flight_​legs
N721WN 12
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N721WN 2002-03-03 06:30 HOU AUS 0 1
N721WN 2002-03-03 07:35 AUS DAL 0 -2
N721WN 2002-03-03 08:55 DAL SAT 0 0
N721WN 2002-03-03 10:20 SAT HOU 0 0
N721WN 2002-03-03 11:30 HOU DAL 0 0
N721WN 2002-03-03 12:50 DAL SAT 0 -5
N721WN 2002-03-03 14:15 SAT HOU 5 0
N721WN 2002-03-03 15:20 HOU SAT 0 4
N721WN 2002-03-03 16:34 SAT HOU 4 -4
N721WN 2002-03-03 17:40 HOU MSY 0 -2
N721WN 2002-03-03 19:10 MSY DAL 5 10
N721WN 2002-03-03 20:55 DAL AMA 5 2
N501SW 11
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N501SW 2002-03-03 09:25 AMA DAL 0 -5
N501SW 2002-03-03 10:45 DAL AUS 0 1
N501SW 2002-03-03 11:55 AUS HRL 0 -4
N501SW 2002-03-03 13:20 HRL HOU 0 0
N501SW 2002-03-03 15:00 HOU DAL 60 52
N501SW 2002-03-03 16:25 DAL AMA 70 70
N501SW 2002-03-03 17:58 AMA DAL 78 75
N501SW 2002-03-03 19:20 DAL HOU 80 84
N501SW 2002-03-03 20:44 HOU HRL 79 85
N501SW 2002-03-03 21:59 HRL HOU 74 72
N501SW 2002-03-03 23:00 HOU DAL 60 68
N722WN 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N722WN 2002-03-03 09:00 MSY HOU 0 -2
N722WN 2002-03-03 10:53 HOU DAL 23 20
N722WN 2002-03-03 12:09 DAL MAF 24 25
N722WN 2002-03-03 13:34 MAF DAL 24 15
N722WN 2002-03-03 14:49 DAL SAT 19 26
N722WN 2002-03-03 16:22 SAT DAL 22 16
N722WN 2002-03-03 17:35 DAL AUS 15 12
N722WN 2002-03-03 18:45 AUS DAL 10 1
N722WN 2002-03-03 19:55 DAL SAT 0 0
N722WN 2002-03-03 21:10 SAT DAL 0 5
N712SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N712SW 2002-03-03 08:40 PBI BNA 0 2
N712SW 2002-03-03 10:17 BNA MDW 7 -2
N712SW 2002-03-03 12:05 MDW DTW 10 5
N712SW 2002-03-03 14:33 DTW MDW 13 15
N712SW 2002-03-03 15:10 MDW STL 20 15
N712SW 2002-03-03 16:44 STL HOU 24 10
N712SW 2002-03-03 19:20 HOU DAL 20 15
N712SW 2002-03-03 20:37 DAL OKC 22 22
N712SW 2002-03-03 21:40 OKC STL 20 13
N712SW 2002-03-03 23:12 STL MDW 12 6
N333SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N333SW 2002-03-03 08:10 FLL MCO 0 -5
N333SW 2002-03-03 09:35 MCO IND 0 -10
N333SW 2002-03-03 12:37 IND MDW 7 4
N333SW 2002-03-03 13:00 MDW BWI 20 21
N333SW 2002-03-03 16:10 BWI ORF 55 60
N333SW 2002-03-03 17:20 ORF BWI 60 48
N333SW 2002-03-03 18:30 BWI ORF 50 58
N333SW 2002-03-03 19:45 ORF MCO 60 67
N333SW 2002-03-03 22:20 MCO BNA 65 52
N333SW 2002-03-03 23:26 BNA MDW 46 43
N301SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N301SW 2002-03-03 06:30 ONT LAS 0 -2
N301SW 2002-03-03 07:58 LAS RNO 8 9
N301SW 2002-03-03 09:34 RNO SJC 4 -2
N301SW 2002-03-03 10:55 SJC ONT 5 -2
N301SW 2002-03-03 12:25 ONT LAS 0 10
N301SW 2002-03-03 13:55 LAS SAN 10 -5
N301SW 2002-03-03 15:10 SAN SJC 0 -3
N301SW 2002-03-03 17:05 SJC LAS 10 12
N301SW 2002-03-03 19:00 LAS PHX 20 20
N301SW 2002-03-03 21:35 PHX STL 20 18
N600WN 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N600WN 2002-03-03 08:00 ELP DAL 0 -12
N600WN 2002-03-03 11:00 DAL TUL 0 -1
N600WN 2002-03-03 12:10 TUL DAL 0 -3
N600WN 2002-03-03 13:25 DAL MAF 0 6
N600WN 2002-03-03 14:55 MAF DAL 5 -1
N600WN 2002-03-03 16:19 DAL SAT 4 0
N600WN 2002-03-03 17:40 SAT HOU 0 -14
N600WN 2002-03-03 18:55 HOU SAT 0 -3
N600WN 2002-03-03 20:10 SAT LAS 0 -2
N600WN 2002-03-03 21:38 LAS LAX 13 -4
N89SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N89SW 2002-03-03 08:00 DAL HOU 0 -3
N89SW 2002-03-03 09:35 HOU DAL 5 0
N89SW 2002-03-03 10:45 DAL OKC 0 -2
N89SW 2002-03-03 11:50 OKC DAL 0 -5
N89SW 2002-03-03 13:05 DAL IAH 0 -8
N89SW 2002-03-03 14:20 IAH DAL 0 -1
N89SW 2002-03-03 15:40 DAL ABQ 0 0
N89SW 2002-03-03 16:55 ABQ AMA 0 5
N89SW 2002-03-03 19:10 AMA DAL 0 0
N89SW 2002-03-03 20:30 DAL IAH 0 4
N664WN 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N664WN 2002-03-03 06:35 SEA SMF 0 -10
N664WN 2002-03-03 08:45 SMF ONT 0 -8
N664WN 2002-03-03 10:24 ONT PHX 4 -3
N664WN 2002-03-03 13:10 PHX SLC 15 14
N664WN 2002-03-03 15:09 SLC BOI 14 5
N664WN 2002-03-03 16:30 BOI PDX 10 6
N664WN 2002-03-03 17:10 PDX OAK 15 1
N664WN 2002-03-03 19:05 OAK LAX 5 -5
N664WN 2002-03-03 21:00 LAX LAS 0 -6
N664WN 2002-03-03 22:30 LAS PHX 0 -6
N90SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N90SW 2002-03-03 10:00 LBB DAL 0 -11
N90SW 2002-03-03 11:25 DAL IAH 0 9
N90SW 2002-03-03 12:45 IAH DAL 0 0
N90SW 2002-03-03 14:00 DAL HOU 0 2
N90SW 2002-03-03 15:25 HOU CRP 5 7
N90SW 2002-03-03 16:40 CRP HOU 15 13
N90SW 2002-03-03 17:38 HOU DAL 8 3
N90SW 2002-03-03 18:45 DAL IAH 0 -5
N90SW 2002-03-03 20:05 IAH DAL 0 -10
N90SW 2002-03-03 21:30 DAL MAF 5 2
N607SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N607SW 2002-03-03 06:50 MAF HOU 0 -7
N607SW 2002-03-03 08:20 HOU JAN 0 -8
N607SW 2002-03-03 09:50 JAN BWI 0 -22
N607SW 2002-03-03 13:25 BWI PVD 0 -7
N607SW 2002-03-03 14:51 PVD BWI 126 126
N607SW 2002-03-03 16:45 BWI ORF 135 146
N607SW 2002-03-03 17:56 ORF JAX 141 148
N607SW 2002-03-03 20:15 JAX ORF 150 120
N607SW 2002-03-03 21:47 ORF BWI 112 95
N607SW 2002-03-03 23:04 BWI ORF 114 120
N311SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N311SW 2002-03-03 07:25 TUS LAS 0 2
N311SW 2002-03-03 08:15 LAS PHX 10 6
N311SW 2002-03-03 10:50 PHX ABQ 20 8
N311SW 2002-03-03 12:15 ABQ AMA 10 12
N311SW 2002-03-03 14:35 AMA ABQ 15 3
N311SW 2002-03-03 14:40 ABQ PHX 0 -14
N311SW 2002-03-03 16:10 PHX SJC 50 55
N311SW 2002-03-03 17:53 SJC LAX 68 53
N311SW 2002-03-03 19:26 LAX PHX 56 44
N311SW 2002-03-03 22:14 PHX ABQ 54 51
N613SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N613SW 2002-03-03 07:25 STL PHX 0 -28
N613SW 2002-03-03 10:30 PHX LAX 0 -14
N613SW 2002-03-03 11:25 LAX SJC 0 -5
N613SW 2002-03-03 13:12 SJC LAS 12 8
N613SW 2002-03-03 14:56 LAS ONT 11 3
N613SW 2002-03-03 16:22 ONT LAS 17 13
N613SW 2002-03-03 17:57 LAS RNO 37 40
N613SW 2002-03-03 19:40 RNO LAS 40 38
N613SW 2002-03-03 21:24 LAS BUR 49 45
N613SW 2002-03-03 22:47 BUR LAS 52 52
N510SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N510SW 2002-03-03 07:45 LAX RNO 0 -5
N510SW 2002-03-03 09:25 RNO BOI 0 -3
N510SW 2002-03-03 11:55 BOI GEG 0 -5
N510SW 2002-03-03 12:20 GEG BOI 0 -2
N510SW 2002-03-03 14:40 BOI RNO 0 -7
N510SW 2002-03-03 15:10 RNO LAX 0 -7
N510SW 2002-03-03 17:05 LAX TUS 10 2
N510SW 2002-03-03 19:40 TUS LAX 0 -6
N510SW 2002-03-03 20:40 LAX SJC 0 1
N510SW 2002-03-03 22:10 SJC PDX 0 -4
N322SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N322SW 2002-03-03 07:45 BDL BWI 0 4
N322SW 2002-03-03 09:15 BWI ALB 0 -8
N322SW 2002-03-03 10:45 ALB BWI 0 0
N322SW 2002-03-03 12:25 BWI SDF 5 10
N322SW 2002-03-03 14:45 SDF STL 15 18
N322SW 2002-03-03 15:15 STL TUL 15 10
N322SW 2002-03-03 16:59 TUL PHX 14 20
N322SW 2002-03-03 19:14 PHX BUR 29 20
N322SW 2002-03-03 19:52 BUR OAK 17 11
N322SW 2002-03-03 21:38 OAK SLC 28 22
N506SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N506SW 2002-03-03 09:16 TPA MSY 6 10
N506SW 2002-03-03 10:40 MSY HOU 20 21
N506SW 2002-03-03 12:15 HOU HRL 30 40
N506SW 2002-03-03 13:41 HRL AUS 46 34
N506SW 2002-03-03 14:51 AUS DAL 21 20
N506SW 2002-03-03 16:06 DAL TUL 16 8
N506SW 2002-03-03 17:21 TUL DAL 6 1
N506SW 2002-03-03 18:45 DAL LBB 15 10
N506SW 2002-03-03 20:05 LBB DAL 5 -3
N506SW 2002-03-03 21:32 DAL MSY 7 5
N93SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N93SW 2002-03-03 10:45 MAF ABQ 0 2
N93SW 2002-03-03 11:24 ABQ ELP 14 12
N93SW 2002-03-03 12:31 ELP SAT 11 14
N93SW 2002-03-03 15:19 SAT HOU 19 6
N93SW 2002-03-03 16:30 HOU AUS 30 23
N93SW 2002-03-03 17:25 AUS MAF 15 14
N93SW 2002-03-03 18:44 MAF AUS 4 0
N93SW 2002-03-03 20:00 AUS HOU 0 -11
N93SW 2002-03-03 21:14 HOU AUS 9 4
N93SW 2002-03-03 22:10 AUS ELP 0 5
N515SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N515SW 2002-03-03 08:05 OKC DAL 0 5
N515SW 2002-03-03 09:50 DAL LBB 0 5
N515SW 2002-03-03 11:22 LBB AUS 12 10
N515SW 2002-03-03 12:35 AUS HOU 0 4
N515SW 2002-03-03 14:02 HOU MSY 12 10
N515SW 2002-03-03 15:48 MSY MCO 33 25
N515SW 2002-03-03 18:40 MCO FLL 25 16
N515SW 2002-03-03 19:50 FLL MCO 10 15
N515SW 2002-03-03 21:10 MCO MSY 10 5
N515SW 2002-03-03 22:15 MSY HOU 5 13
N365SW 10
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N365SW 2002-03-03 07:25 ALB BWI 0 0
N365SW 2002-03-03 09:05 BWI BNA 0 9
N365SW 2002-03-03 10:35 BNA LAS 5 14
N365SW 2002-03-03 13:32 LAS BUR 22 18
N365SW 2002-03-03 15:09 BUR OAK 34 33
N365SW 2002-03-03 16:56 OAK BUR 46 45
N365SW 2002-03-03 18:24 BUR SMF 49 54
N365SW 2002-03-03 20:09 SMF BUR 59 55
N365SW 2002-03-03 21:27 BUR LAS 47 41
N365SW 2002-03-03 22:36 LAS ABQ 36 34
N671SW 9
tail_​num dep_​minute origin_​code dest_​code dep_​delay arr_​delay
N671SW 2002-03-03 09:00 DTW MDW 0 8
N671SW 2002-03-03 09:40 MDW LIT 20 22
N671SW 2002-03-03 11:48 LIT HOU 28 33
N671SW 2002-03-03 13:46 HOU SAT 46 46
N671SW 2002-03-03 15:04 SAT DAL 54 47
N671SW 2002-03-03 16:18 DAL LIT 53 47
N671SW 2002-03-03 17:38 LIT STL 53 52
N671SW 2002-03-03 19:09 STL MDW 49 56
N671SW 2002-03-03 20:58 MDW DTW 68 60

The Malloy Model

All of the queries above are executed against the following model:

define airports is (explore 'malloy-data.faa.airports'
  primary key code
  name is concat(code, ' - ', full_name)
  airport_count is count()
);

define carriers is (explore 'malloy-data.faa.carriers'
  primary key code
  carrier_count is count()
);

define aircraft_models is (explore 'malloy-data.faa.aircraft_models'
  primary key aircraft_model_code
  aircraft_model_count is count()
);

define aircraft is (explore 'malloy-data.faa.aircraft'
  primary key tail_num
  aircraft_count is count()
  -- joins
  aircraft_models is join on aircraft_model_code
);

export define flights is (explore 'malloy-data.faa.flights'
  primary key id2
  -- rename some fields
  origin_code renames origin
  destination_code renames destination

  -- joins
  carriers is join on carrier
  origin is join airports on origin_code
  destination is join airports on destination_code,
  aircraft is join on tail_num

  -- measures
  flight_count is count()
  total_distance is sum(distance)
  seats_for_sale is sum(aircraft.aircraft_models.seats)
  seats_owned is aircraft.sum(aircraft.aircraft_models.seats)

  -- queries
  measures is (reduce
    flight_count
    aircraft.aircraft_count
    dest_count is destination.airport_count
    origin_count is origin.airport_count
  )

  -- shows carriers and number of destinations (bar chart)
  by_carrier is (reduce
    carriers.nickname
    flight_count
    destination_count is destination.count()
  )

  -- shows year over year growth (line chart)
  year_over_year is (reduce
    dep_month is month(dep_time)
    flight_count
    dep_year is dep_time.year
  )

  -- shows plane manufacturers and frequency of use
  by_manufacturer is (reduce top 20
    aircraft.aircraft_models.manufacturer
    aircraft.aircraft_count
    flight_count
  )

  delay_by_hour_of_day is (reduce : [dep_delay >30]
    dep_hour is hour(dep_time)
    flight_count
    delay is FLOOR(dep_delay)/30 * 30
  )

  carriers_by_month is (reduce
    dep_month is dep_time.month
    flight_count
    carriers.nickname
  )

  seats_by_distance is (reduce
    seats is floor(aircraft.aircraft_models.seats/5)*5 -- rounded to 5
    flight_count
    distance is floor(distance/20)*20 -- rounded to 20
  )

  routes_map is (reduce
    origin.latitude
    origin.longitude
    latitude2 is destination.latitude
    longitude2 is destination.longitude
    flight_count
  )

  destinations_by_month is (reduce
    dep_time.`month`
    flight_count
    destination.name
  )

  -- explore flights : [origin.code : 'SJC'] | airport_dashboard
  airport_dashboard is ( reduce top 10
    code is destination_code
    destination is destination.full_name
    flight_count
    carriers_by_month
    routes_map
    delay_by_hour_of_day
  )

  -- explore flights : [carriers.nickname : 'Southwest'] | carrier_dashboard
  carrier_dashboard is ( reduce
    destination_count is destination.airport_count
    flight_count
    by_manufacturer
    by_month is (reduce
      dep_month is dep_time.month
      flight_count
    )
    hubs is (reduce : [destination.airport_count > 1] top 10
      hub is origin.name
      destination_count is destination.airport_count
    )
    origin_dashboard is (reduce top 10
      code is origin_code
      origin is origin.full_name
      origin.city
      flight_count
      destinations_by_month
      routes_map
      year_over_year
    )
  )

  detail is (project top 30 order by 2
    id2, dep_time, tail_num, carrier, origin_code, destination_code, distance
    aircraft.aircraft_model_code
  )

  -- query that you might run for to build a flight search interface
  -- explore flights : [origin.code: 'SJC', destination.code:'LAX'|'BUR', dep_time: @2004-01-01] | kayak
  kayak is (reduce
    carriers is (reduce
      carriers.nickname
      flight_count
    )
    by_hour is (reduce order by 1
      dep_hour is hour(dep_time)
      flight_count
    )
    flights is (reduce
      dep_minute is dep_time.minute
      carriers.name
      flight_num
      origin_code
      destination_code
      aircraft.aircraft_models.manufacturer
      aircraft.aircraft_models.model
    )
  )

  -- example query that shows how you can build a map reduce job to sessionize flights
  sessionize is (reduce : [carrier:'WN', dep_time: @2002-03-03]
    dep_time.`date`
    carrier
    flight_count
    plane is (reduce top 20
      tail_num
      flight_count
      flights is (reduce order by 2
        tail_num
        dep_minute is dep_time.minute
        origin_code
        destination_code
        )
    )
  )

  search_index is (index : [dep_time: @2004-01]
    *, carriers.*,
    origin.code, origin.state, origin.city, origin.full_name, origin.fac_type
    destination.code, destination.state, destination.city, destination.full_name
    aircraft.aircraft_model_code, aircraft.aircraft_models.manufacturer
    aircraft.aircraft_models.model
    on flight_count
  )
);

Data Styles

The data styles tell the Malloy renderer how to render different kinds of results.

{
  "by_carrier": {
    "renderer": "bar_chart"
  },
  "year_over_year": {
    "renderer": "line_chart"
  },
  "by_month": {
    "renderer": "line_chart"
  },
  "by_manufacturer": {
    "renderer": "bar_chart"
  },
  "routes_map": {
    "renderer": "segment_map"
  },
  "destinations_by_month": {
    "renderer": "line_chart"
  },
  "delay_by_hour_of_day": {
    "renderer" : "scatter_chart"
  },
  "seats_by_distance": {
    "renderer": "scatter_chart"
  },
  "carriers_by_month" : {
    "renderer": "line_chart"
  }
}